﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace Tilver.Data.DataPager
{
    internal class MySqlPager : IPager
    {
        /// <summary>
        /// 分页语句缓存
        /// </summary>
        private static Dictionary<string, SqlPagerContext> _pagerSqlCache = new Dictionary<string, SqlPagerContext>();
        private static readonly object _lock = new object();
        private readonly SqlPagerContext _context;
        private DbDataBase _dataBase;
        private IList<DbParameter> _params;
        private long _pageSize;

        public MySqlPager(string sql, long pageSize) : this(sql, pageSize, string.Empty, DbDataFactory.Create()) { }
        public MySqlPager(string sql, long pageSize, string keyField) : this(sql, pageSize, keyField, DbDataFactory.Create()) { }
        public MySqlPager(string sql, long pageSize, string keyField, string connectionStringName) : this(sql, pageSize, keyField, DbDataFactory.Create(connectionStringName)) { }
        public MySqlPager(string sql, long pageSize, DbDataBase database) : this(sql, pageSize, string.Empty, database) { }
        public MySqlPager(string sql, long pageSize, string keyField, DbDataBase database)
        {
            _dataBase = database;
            _pageSize = pageSize;

            var key = string.Format("{0}_{1}_{2}", database.ConnectionString, sql, pageSize);
            if (!_pagerSqlCache.ContainsKey(key))
            {
                lock (_lock)
                {
                    if (!_pagerSqlCache.ContainsKey(key))
                    {

                        var item = new SqlPagerContext { PageSize = pageSize };
                        var sqlStruct = new SqlParser().Parse(sql);
                        if (string.IsNullOrEmpty(sqlStruct.Select)) sqlStruct.Select = "*";
                        if (!string.IsNullOrEmpty(sqlStruct.Where)) sqlStruct.Where = string.Format("WHERE {0}", sqlStruct.Where);
                        if (!string.IsNullOrEmpty(sqlStruct.GroupBy)) sqlStruct.GroupBy = string.Format("GROUP BY {0}", sqlStruct.GroupBy);
                        if (!string.IsNullOrEmpty(sqlStruct.Having)) sqlStruct.Having = string.Format("HAVING {0}", sqlStruct.Having);
                        if (!string.IsNullOrEmpty(sqlStruct.OrderBy)) sqlStruct.OrderBy = string.Format("ORDER BY {0}", sqlStruct.OrderBy);
                        //var fields = GetFieldsAlias(sqlStruct.Fields);
                        var hasGroupBy = !string.IsNullOrEmpty(sqlStruct.GroupBy);

                        // 获得KeyField
                        //keyField = (keyField == null) ? string.Empty : keyField.Trim();
                        //if (string.IsNullOrEmpty(keyField) && !hasGroupBy)
                        //{
                        //    keyField = SqlPagerUtil.GetKeyField(database, sqlStruct.From);
                        //}
                        sqlStruct.From = "FROM " + sqlStruct.From;

                        // RecordCountSql
                        if (hasGroupBy)
                            item.RecordCountSql = string.Format("SELECT COUNT(*) FROM (SELECT {0} {1} {2} {3} {4}) Tilver_Temp_Table_A"
                                , sqlStruct.Select, sqlStruct.From, sqlStruct.Where, sqlStruct.GroupBy, sqlStruct.Having);
                        else
                            item.RecordCountSql = string.Format("SELECT COUNT(*) {0} {1}", sqlStruct.From, sqlStruct.Where);

                        // FirstPageSql
                        if (hasGroupBy)
                            item.FirstPageSql = string.Format("SELECT {0} {1} {2} {3} {4} {5} LIMIT @PageSize"
                                , sqlStruct.Select, sqlStruct.From, sqlStruct.Where, sqlStruct.GroupBy, sqlStruct.Having, sqlStruct.OrderBy);
                        else
                            item.FirstPageSql = string.Format("SELECT {0} {1} {2} {3} LIMIT @PageSize", sqlStruct.Select, sqlStruct.From, sqlStruct.Where, sqlStruct.OrderBy);

                        string id1 = "@PageOffset";
                        string id2 = "@PageSize";
                        // PagerSql
                        item.PagerSql = string.Format("SELECT * FROM (SELECT {1} {2} {3} {4} {5} {0}) Tilver_Temp_Table_A LIMIT {6},{7} ", sqlStruct.OrderBy, sqlStruct.Select, sqlStruct.From, sqlStruct.Where, sqlStruct.GroupBy, sqlStruct.Having, id1, id2);

                        _pagerSqlCache.Add(key, item);
                    }
                }
            }
            _context = _pagerSqlCache[key];
        }



        public long GetRecordCount()
        {
            var ado = new DbAdoBase(_context.RecordCountSql, _dataBase);
            var count = ado.AddParameters(_params).ExecuteScalar();
            ado.ClearParameters();
            return Convert.ToInt64(count);
        }

        public DbDataReader GetPageData(long pageIndex)
        {
            DbDataReader ret;
            var temPara = new List<MySql.Data.MySqlClient.MySqlParameter>();
            {
                var p = new MySqlParameter("@PageSize", MySqlDbType.Int64);
                p.Value = _pageSize;
                temPara.Add(p);
            }

            var sql = string.Empty;
            if (pageIndex <= 1)
            {
                sql = _context.FirstPageSql;
                var ado = new DbAdoBase(sql, _dataBase);
                ret = ado
                    .AddParameters(_params)
                    .AddParameters(temPara)
                    .ExecuteReader();
                ado.ClearParameters();
            }
            else
            {
                sql = _context.PagerSql;
                {
                    var p = new MySqlParameter("@PageOffset", MySqlDbType.Int64);
                    p.Value = (pageIndex - 1) * _pageSize; ;
                    temPara.Add(p);
                }

                var ado = new DbAdoBase(sql, _dataBase);
                ret = ado
                    .AddParameters(_params)
                    .AddParameters(temPara)
                    .ExecuteReader();
                ado.ClearParameters();
            }
            return ret;
        }

        public void AddParameters(IList<DbParameter> paras)
        {
            _params = paras;
        }
    }
}
